package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.ProductclassifiCationTable;
import com.ysd.entity.Productdetailsinfo;

import com.ysd.util.DBManager;

public class ProductdetailsinfoDaoImpl {
	public List<Productdetailsinfo> Productdetailsinfo(int pid){
		List<Productdetailsinfo> list = new ArrayList<Productdetailsinfo>();
		Connection conn = DBManager.grtConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			
			rs = st.executeQuery("select * from Productdetailsinfo where cid="+pid);
			while(rs.next()) {
				String productProductName = rs.getString("productProductName");
				int cid = rs.getInt("cid");
				String img = rs.getString("img");
				int productid = rs.getInt("productid");

				Productdetailsinfo  Productdetailsinfo = new Productdetailsinfo(productProductName, cid,img,productid);
				list.add(Productdetailsinfo);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
	} 
	/**
	 * 多条件查询产品详情
	 * 
	 * */

	public List<Productdetailsinfo> getProductdetailsinfo(Productdetailsinfo p){
		List<Productdetailsinfo>list = new ArrayList<Productdetailsinfo>();
		Connection conn = DBManager.grtConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "select p.*,pc.productcategory from productdetailsinfo p, productclassificationtable pc where 1=1";
			if (p.getProductid()>0) {//型号
				sql +=" and productid like '%"+p.getProductid()+"%'";
			}
			if (p.getProductProductName()!=null && !p.getProductProductName().equals("")) {
				sql +=" and productProductName like '%"+p.getProductProductName()+"%'";//名称
			}
			
			if (p.getBeginproductPrice()>0 ) {
				sql +=" and productPrice<"+p.getBeginproductPrice();//最低价
			}
			if (p.getEndproductPrice()>0 ) {
				sql +=" and productPrice>"+p.getEndproductPrice();//最高价
			}
			if (p.getProductBrand()!=null && !p.getProductBrand().equals("")) {
				sql += " and ProductBrand='"+p.getProductBrand()+"'";
			}
			if (p.getColor()!=null && !p.getColor().equals("")) {
				sql +=" and color like '%红%'";//颜色
			}
			if (p.getcId()>0) {
				sql +=" and cId = "+p.getcId();
			}
			sql+=" and p.cId=pc.productclassid";
			System.out.println(sql);
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int productid = rs.getInt("productid");
				String productProductName = rs.getString("productProductName");
				int productPrice = rs.getInt("productPrice");
				String productBrand = rs.getString("productBrand");
				String color = rs.getString("color");
				int cId = rs.getInt("cId");
				String productcategory = rs.getString("productcategory");
				String img = rs.getString("img");
				String productMaterial = rs.getString("productMaterial");
				int marketPrice = rs.getInt("marketPrice");
				
				String style = rs.getString("style");
				Productdetailsinfo  Productdetailsinfo = new Productdetailsinfo(productProductName, cId, img, productid, productMaterial, 0, 0, productPrice, marketPrice, productBrand, color, style, productcategory);
				list.add(Productdetailsinfo);
			}
			return list;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	/**
	 * 多条件分页查询产品详情
	 * 
	 * */

	public List<Productdetailsinfo> getProductdetailsinfoByPage(Productdetailsinfo p , int page , int size){
		List<Productdetailsinfo>list = new ArrayList<Productdetailsinfo>();
		Connection conn = DBManager.grtConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "select p.*,pc.productcategory from productdetailsinfo p, productclassificationtable pc where 1=1";
			if (p.getProductid()>0) {//型号
				sql +=" and productid like '%"+p.getProductid()+"%'";
			}
			if (p.getProductProductName()!=null && !p.getProductProductName().equals("")) {
				sql +=" and productProductName like '%"+p.getProductProductName()+"%'";//名称
			}
			
			if (p.getBeginproductPrice()>0 ) {
				sql +=" and productPrice<"+p.getBeginproductPrice();//最低价
			}
			if (p.getEndproductPrice()>0 ) {
				sql +=" and productPrice>"+p.getEndproductPrice();//最高价
			}
			if (p.getProductBrand()!=null && !p.getProductBrand().equals("")) {
				sql += " and ProductBrand='"+p.getProductBrand()+"'";
			}
			if (p.getColor()!=null && !p.getColor().equals("")) {
				sql +=" and color like '%红%'";//颜色
			}
			if (p.getcId()>0) {
				sql +=" and cId = "+p.getcId();
			}
			sql+=" and p.cId=pc.productclassid limit "+(page-1)*size+","+size;
			System.out.println(sql);
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int productid = rs.getInt("productid");
				String productProductName = rs.getString("productProductName");
				int productPrice = rs.getInt("productPrice");
				String productBrand = rs.getString("productBrand");
				String color = rs.getString("color");
				int cId = rs.getInt("cId");
				String productcategory = rs.getString("productcategory");
				String img = rs.getString("img");
				String productMaterial = rs.getString("productMaterial");
				int marketPrice = rs.getInt("marketPrice");
				
				String style = rs.getString("style");
				Productdetailsinfo  Productdetailsinfo = new Productdetailsinfo(productProductName, cId, img, productid, productMaterial, 0, 0, productPrice, marketPrice, productBrand, color, style, productcategory);
				list.add(Productdetailsinfo);
			}
			return list;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}finally {
			DBManager.closeAll(conn, st, rs);
		}
		
	}
	//根据分类id删除产品
		public int delcanpin(int cId) {
			return DBManager.executeUpdate("DELETE from productdetailsinfo where productid="+cId);
			
		}
		//增加
		public int addinfo(Productdetailsinfo pc) {
			//把Productdetailsinfo封装到pc，sql语句，调用实体类中的方法
			return DBManager.executeUpdate("INSERT into productdetailsinfo VALUES(null,'"+pc.getProductProductName()+"','"+pc.getProductMaterial()+"','"+pc.getProductPrice()+"','"+pc.getMarketPrice()+"','"+pc.getProductBrand()+"','"+pc.getImg()+"','"+pc.getColor()+"','"+pc.getStyle()+"','"+pc.getcId()+"')");
		}
		
		public List<Productdetailsinfo> getPinfo(String name){
			
			List<Productdetailsinfo> pcList = new ArrayList<Productdetailsinfo>();
			//创建集合现在位空在下面add添加
			Connection conn = DBManager.grtConnection();
			Statement st = null;
			ResultSet rs = null;
			try {
				st = conn.createStatement();
				//创建Statement对象，用来执行执行语句
				rs = st.executeQuery("select * from productdetailsinfo where productProductName = '"+name+"'");
				//执查询到的sql语句
				while (rs.next()) {
					//默认指针第0行
					//便利结果集
					int productid = rs.getInt("productid");
					//从数据库来可以写1
					String productProductName = rs.getString("productProductName");
					String productMaterial = rs.getString("productMaterial");
					int productPrice = rs.getInt("productPrice");
					int marketPrice = rs.getInt("marketPrice");
					String productBrand = rs.getString("productBrand");
					String img = rs.getString("img");
					String color = rs.getString("color");
					String style = rs.getString("style");
					int cId = rs.getInt("cId");
					Productdetailsinfo pc = new Productdetailsinfo(productProductName, cId, img, productid, productMaterial, productPrice, marketPrice, productBrand, color, style);
					//将获取到的数据封装到pc
					pcList.add(pc);
				}
				return pcList;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}finally {
				DBManager.closeAll(conn, st, rs);
			}
		}
		public Productdetailsinfo getproductbyid(int cateId) {
			Productdetailsinfo pc = null;
			Connection conn = DBManager.grtConnection();
			Statement st = null;
			ResultSet rs = null;
			try {
				st = conn.createStatement();
				rs= st.executeQuery("select * from Productdetailsinfo where productid="+cateId);
				while (rs.next()) {
					int productid = rs.getInt(1);
					String productProductName = rs.getString(2);
					String productMaterial = rs.getString(3);
					int productPrice = rs.getInt(4);
					int marketPrice = rs.getInt(5);
					String productBrand = rs.getString(6);
					String img = rs.getString(7);
					String color = rs.getString(8);
					String style = rs.getString(9);
					int cId = rs.getInt(10);
					pc = new Productdetailsinfo(productProductName, cId, img, productid, productMaterial, productPrice, marketPrice, productBrand, color, style);
				}
				return pc;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}finally {
				DBManager.closeAll(conn, st, rs);
			}
		}
		public int updateProductdetailsinfo(Productdetailsinfo pc) {
			return DBManager.executeUpdate("update productdetailsinfo set productProductName ='"+pc.getProductProductName()+"',productBrand='"+pc.getProductBrand()+"',productMaterial='"+pc.getProductMaterial()+"' ,productPrice='"+pc.getProductPrice()+"',marketPrice='"+pc.getMarketPrice()+"',img='"+pc.getImg()+"',color='"+pc.getColor()+"',style='"+pc.getStyle()+"',cId='"+pc.getcId()+"'  where productid="+pc.getProductid());
		}

}